/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package azure;

/**
 * Overcoming one of the limitations of SQLite, I've created the equivalent of Stored Procedures here.
 * Though these cannot be compiled by the database engine, the statements should not change, so this will save code duplication.
 * @author Gargravarr
 * @date Aug 10, 2011
 */
public class StoredProcs
{

    /**
     * Return an SQLite Prepared Statement for updating one of the possible tables
     * @param statementID
     * @return 
     */
    public static String getUpdatePreparedStatement(int statementID)
    {
	switch (statementID)
	{
	    case Enums.MANGA:
		return "UPDATE Mangas SET "
			+ "MangaName=(?),"
			+ "Description=(?),"
			+ "Publisher=(?),"
			+ "PublishedYear=(?),"
			+ "GenreID=(?) WHERE ID=(?)";
	    case Enums.VOLUME:
		return "UPDATE Volumes SET "
			+ "VolumeNo=(?),"
			+ "VolumeDescription=(?),"
			+ "VolumeYear=(?) WHERE VolumeID=(?)";
	    case Enums.CHAPTER:
		return "UPDATE Chapters SET ChapterNo=(?), ChapterVersion=(?), ChapterDesc=(?), VolumeID=(?) WHERE ChapterID=(?)";
	    case Enums.PAGE:
		return "UPDATE Pages SET "
			+ "PageNo=(?),"
			+ "Filename=(?),"
			+ "LastReadDate=(?)"
			+ " WHERE PageID=(?)";
	    case Enums.SETTINGS:
		return "UPDATE SETTINGS SET SettingValue=(?) WHERE SettingName=(?)";
	    case Enums.PUBLISHER:
		return "UPDATE Publishers SET PublisherName=(?), PublisherCountry=(?) WHERE PublisherID=(?)";
	    default:
		return "";
	}
    }

    public static String getInsertPreparedStatement(int statementID)
    {
	switch (statementID)
	{
	    case Enums.MANGA:
		return "INSERT INTO Mangas(MangaID, MangaName, Description, Publisher, PublishedYear, GenreID) "
			+ "VALUES((?),(?),(?),(?),(?),(?))";
	    case Enums.VOLUME:
		return "INSERT INTO Volumes(MangaLibraryID, VolumeNo, VolumeDescription, VolumeYear) VALUES ((?),(?),(?),(?))";
	    case Enums.CHAPTER:
		return "INSERT INTO Chapters(ChapterNo, MangaLibraryID, VolumeID, ChapterVersion) "
			+ "VALUES((?), (?), (?), (?))";
	    case Enums.PAGE:
		return "INSERT INTO Pages(MangaLibraryID, ChapterID, PageNo, Filename) "
			+ "VALUES((?), (?), (?), (?))";
	    case Enums.SETTINGS:
		return "INSERT INTO Settings VALUES((?), (?))";
	    case Enums.PUBLISHER:
		return "INSERT INTO Publishers(PublisherName, PublisherCountry) VALUES ((?), (?))";
	    default:
		return "";
	}
    }
    
    public static String getDeleteStatement(int statementID, int IDNo)
    {
	switch (statementID)
	{
	    case Enums.MANGA:
		return "DELETE FROM Mangas WHERE LibraryID=" + IDNo;
	    case Enums.VOLUME:
		return "DELETE FROM Volumes WHERE VolumeID=" + IDNo;
	    case Enums.CHAPTER:
		return "DELETE FROM Chapters WHERE ChapterID=" + IDNo;
	    case Enums.PAGE:
		return "DELETE FROM Pages WHERE PageID=" + IDNo;
	    case Enums.PUBLISHER:
		return "DELETE FROM Publishers WHERE PublisherID=" + IDNo;
	    default:
		return "";
	}
    }

    public static String getDeleteAllStatement(int statementID)
    {
	switch (statementID)
	{
	    case Enums.PAGE:
		return "DELETE FROM Pages";
	    case Enums.VOLUME:
		return "DELETE FROM Volumes";
	    case Enums.CHAPTER:
		return "DELETE FROM Chapters";
	    case Enums.MANGA:
		return "DELETE FROM Mangas";
	    default:
		return "";
	}
    }

    public static String selectLibraryKey()
    {
	return "SELECT SettingValue FROM Settings WHERE SettingName='LibraryKey'";
    }

    public static String selectAllMangas()
    {
	return "SELECT * FROM Mangas WHERE ID>0";
    }

    public static String selectChaptersForLibraryIDAndVolume(int libraryID, int volumeID)
    {
	return "SELECT * FROM Chapters WHERE MangaLibraryID=" + libraryID + " AND VolumeID=" + volumeID + " ORDER BY ChapterNo,ChapterVersion";
    }
    
    public static String selectAllVolumesForLibraryID(int libraryID)
    {
	return "SELECT * FROM Volumes WHERE MangaLibraryID=" + libraryID + " ORDER BY VolumeNo";
    }

    public static String selectAllPagesForLibraryIDAndChapter(int libraryID, int chapterID)
    {
	return "SELECT * FROM Pages WHERE MangaLibraryID=" + libraryID + " AND ChapterID=" + chapterID + " ORDER BY PageNo";
    }

    public static String getMaxChapterID()
    {
	return "SELECT MAX(ChapterID) AS ID FROM Chapters";
    }
    
    public static String getMaxVolumeID()
    {
	return "SELECT MAX(VolumeID) AS ID FROM Volumes";
    }

    public static String getMaxMangaID()
    {
	return "SELECT MAX(ID) AS ID FROM Mangas";
    }

    public static String[] getDatabaseCreationStrings()
    {
	return new String[]
		{
		    "CREATE TABLE Settings (SettingName TEXT PRIMARY KEY, SettingValue TEXT);",
		    "CREATE TABLE Mangas (ID INTEGER, MangaID INTEGER, MangaName TEXT,  Description TEXT, Publisher INTEGER, PublishedYear INTEGER, GenreID INTEGER,PRIMARY KEY(\"ID\"),CONSTRAINT \"MangaID\" UNIQUE (MangaID COLLATE NOCASE ASC));",
		    "CREATE TABLE Volumes(VolumeID INTEGER PRIMARY KEY, MangaLibraryID INTEGER, VolumeNo INTEGER, VolumeDescription TEXT, VolumeYear INTEGER, CONSTRAINT \"MangaVolume\" FOREIGN KEY (\"MangaLibraryID\") REFERENCES \"Mangas\" (\"ID\") ON DELETE CASCADE ON UPDATE CASCADE);",
		    "CREATE TABLE Chapters (ChapterID INTEGER, MangaLibraryID INTEGER, VolumeID INTEGER, ChapterNo INTEGER,ChapterDesc TEXT,ChapterVersion INTEGER,PRIMARY KEY(\"ChapterID\"),CONSTRAINT \"MangaChapter\" FOREIGN KEY (\"MangaLibraryID\") REFERENCES \"Mangas\" (\"ID\") ON DELETE CASCADE ON UPDATE CASCADE);",
		    "CREATE TABLE Pages (PageID INTEGER,MangaLibraryID INTEGER,ChapterID INTEGER,PageNo INTEGER,Filename TEXT, LastReadDate TEXT, PRIMARY KEY(PageID),CONSTRAINT \"ChapterPage\" FOREIGN KEY (\"ChapterID\") REFERENCES \"Chapters\" (\"ChapterID\") ON DELETE CASCADE ON UPDATE CASCADE);",
		    "CREATE TABLE ErrorLog (ErrorID INTEGER PRIMARY KEY, Class TEXT, LineNumber INTEGER, DateStamp TEXT, ErrorText TEXT, Reported INT)",
		    "CREATE TABLE Publishers(PublisherID INTEGER PRIMARY KEY, PublisherName TEXT, PublisherCountry INTEGER)"
		};
    }

    public static String[] getSettingsStrings()
    {
	return new String[]
		{
		    "Language",
		    "LastOpenMangaID",
		    "LastOpenVolumeID",
		    "LastOpenMangaChapterID",
		    "LastOpenPageID",
		    "WindowX",
		    "WindowY",
		    "WindowWidth",
		    "WindowHeight",
		    "DefaultDirectory",
		    "AutoResume",
		    "LibraryKey",
		    "ImageLabelX",
		    "ImageLabelY"
		};
    }

    public static String logError(String jClass, int lineNumber, String dateStamp, String errorText)
    {
	return "INSERT INTO ErrorLog(Class, LineNumber, DateStamp, ErrorText, Reported) VALUES('" + jClass + "'," + lineNumber + ",'" + dateStamp + "','" + errorText + "'," + "0)";
    }

    public static String getPublishers()
    {
	return "SELECT * FROM Publishers";
    }

    public static String getPublisherForPublisherID(int publisherID)
    {
	return "SELECT PublisherName FROM Publishers WHERE PublisherID=" + publisherID;
    }
}
